import { RowDataPacket } from 'mysql2';
import { BaseModel } from './base.model';
import pool from '../config/database';

interface TrafficData extends RowDataPacket {
  id: number;
  source: string;
  medium: string;
  campaign: string;
  visitors: number;
  page_views: number;
  conversions: number;
  bounce_rate: number;
  date: Date;
  created_at: Date;
  updated_at: Date;
}

export class TrafficModel extends BaseModel {
  constructor() {
    super('traffic_data');
  }

  // 获取指定日期范围的流量数据
  async getTrafficByDateRange(startDate: string, endDate: string) {
    const sql = `
      SELECT 
        date,
        SUM(visitors) as total_visitors,
        SUM(page_views) as total_page_views,
        SUM(conversions) as total_conversions,
        AVG(bounce_rate) as avg_bounce_rate
      FROM ${this.tableName}
      WHERE date BETWEEN ? AND ?
      GROUP BY date
      ORDER BY date ASC
    `;

    const [rows] = await pool.query<TrafficData[]>(sql, [startDate, endDate]);
    return rows;
  }

  // 获取流量来源分布
  async getTrafficSourceDistribution(startDate: string, endDate: string) {
    const sql = `
      SELECT 
        source,
        SUM(visitors) as total_visitors,
        SUM(conversions) as total_conversions,
        (SUM(conversions) / SUM(visitors) * 100) as conversion_rate
      FROM ${this.tableName}
      WHERE date BETWEEN ? AND ?
      GROUP BY source
      ORDER BY total_visitors DESC
    `;

    const [rows] = await pool.query<TrafficData[]>(sql, [startDate, endDate]);
    return rows;
  }

  // 获取转化率趋势
  async getConversionTrend(startDate: string, endDate: string) {
    const sql = `
      SELECT 
        date,
        source,
        (SUM(conversions) / SUM(visitors) * 100) as conversion_rate
      FROM ${this.tableName}
      WHERE date BETWEEN ? AND ?
      GROUP BY date, source
      ORDER BY date ASC, source
    `;

    const [rows] = await pool.query<TrafficData[]>(sql, [startDate, endDate]);
    return rows;
  }

  // 批量创建流量数据
  async batchCreate(data: Partial<TrafficData>[]) {
    if (!data.length) return { affectedRows: 0 };

    const keys = Object.keys(data[0]);
    const values = data.map(item => Object.values(item));
    const sql = `INSERT INTO ${this.tableName} (${keys.join(', ')}) VALUES ?`;

    const [result] = await pool.query<ResultSetHeader>(sql, [values]);
    return result;
  }

  // 获取流量数据汇总
  async getTrafficSummary(startDate: string, endDate: string) {
    const sql = `
      SELECT 
        COUNT(DISTINCT source) as source_count,
        SUM(visitors) as total_visitors,
        SUM(page_views) as total_page_views,
        SUM(conversions) as total_conversions,
        (SUM(conversions) / SUM(visitors) * 100) as overall_conversion_rate,
        AVG(bounce_rate) as avg_bounce_rate
      FROM ${this.tableName}
      WHERE date BETWEEN ? AND ?
    `;

    const [rows] = await pool.query<TrafficData[]>(sql, [startDate, endDate]);
    return rows[0];
  }
} 